--A. 现要求对“ATM柜员机系统”进行数据库的设计并实现，数据库保存在D:\bank目录下，文件增长率为15% 。
create database ATM
on
(
	name = 'ATM',
	filename = 'D:\bank\ATM.mdf',
	size = 5mb,
	maxsize = 50mb,
	filegrowth = 15%
)
log on
(
	name = 'ATM_log',
	filename = 'D:\bank\ATM_log.ldf',
	size = 5mb,
	maxsize = 50mb,
	filegrowth = 15%
)
go
--B. 根据下图创建表，约束种类参考下列表的说明
--用户信息表：userInfo ：
--字段名称	说  明
--customerID	顾客编号	自动编号（标识列），从1开始，主键
--customerName	开户名	必填
--PID	身份证号	必填，只能是18位或15位，身份证号唯一约束
--telephone	联系电话	必填，格式为xxxx-xxxxxxxx或手机号13位
--address	居住地址	可选输入

--银行卡信息表：cardInfo
--字段名称	说  明
--cardID	卡号	必填，主健，银行的卡号规则和电话号码一样，一般前8位代表特殊含义，如某总行某支行等。假定该行要求其营业厅的卡号格式为：1010 3576 xxxx xxx开始,每4位号码后有空格，卡号一般是随机产生。
--curType	货币种类	必填，默认为RMB
--savingType	存款类型	活期/定活两便/定期
--openDate	开户日期	必填，默认为系统当前日期
--balance	余额	必填，不低于1元
--pass	密码	必填，6位数字，开户时默认为6个“8”
--IsReportLoss	是否挂失	必填，是/否值，默认为”否”
--customerID	顾客编号	外键，必填，表示该卡对应的顾客编号，一位顾客允许办理多张卡号

--交易信息表：transInfo 
--字段名称	说  明
--transId	交易编号	标识列、主键
--transDate	交易日期	必填，默认为系统当前日期
--cardID	卡号	必填，外健，可重复
--transType 	交易类型	必填，只能是存入/支取
--transMoney	交易金额	必填，大于0
--remark	备注	可选输入，其他说明

use ATM
go

create table userInfo
(
	customerID	int primary key identity(1,1),							--顾客编号
	customerName	nvarchar(10) not null ,								--开户名	
	PID	char(20) not null check (len(PID)=15 or len(PID)=18) unique,	--身份证号	
	telephone	char(13) not null 
	check(telephone like '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'or 
	telephone not like '%[^0-9]%'and len(telephone)=13 ),				--联系电话
	address nvarchar(30) null,											--居住地址	
)
create table cardInfo
	(
	cardID		char(19) primary key check(cardid like '1010 3576 [0-9][0-9][0-9][0-9] [0-9][0-9][0-9]' )
	default ('1010 3576'+right('0000'+cast(floor(rand()*10000)as varchar),4)+' '+right('000'+cast(floor(rand()*1000)as varchar),3)), 
	--卡号	
	curType		char(5) default 'RMB' not null,							--货币种类	
	savingType	char(8) check(savingType in ('活期','定活两便','定期')),--存款类型	
	openDate	datetime default getdate(),								--开户日期	
	balance	    money check(balance>=1)not null,						--余额		
	pass		char(6)	default('888888') not null,						--密码		
	IsReportLoss	varchar(2) 
	default '否' check(IsReportLoss='是'or IsReportLoss='否')not null,	--是否挂失	
	customerID	int references userInfo(customerID)						--顾客编号	
)
create table transinfo													
(
	  id int identity(1,1)Constraint PK_Transinfo_Id Primary Key,		--ID主键
	  transdate   datetime default getdate() not null,					--交易日期
	  transtype   char(4) check(transtype in ('存入','支取'))not null,	--交易类型
	  cardid  	  char(19) references cardInfo(cardID) not null,		--卡号
	  transmoney  money check(transmoney>0)	not null,					--交易金额
	  remark  	  text													--备注
)
	--C. 根据下列条件插入和更新测试数据
	--孙悟空开户，身份证：123456789012345，电话：0716-78989783，地址：北京海淀 
	--   开户金额：1000 活期   卡号：1010 3576 1234 567

	--沙和尚开户，身份证：421345678912345678，电话：0478-44223333，
	--   开户金额： 1  定期 卡号：1010 3576 1212 117

	--唐僧开户，身份证：321245678912345678，电话：0478-44443333，
	--   开户金额： 1  定期 卡号：1010 3576 1212 113

insert into userInfo
	values('孙悟空','123456789012345','0716-78989783','北京海淀 '),
		  ('沙和尚','421345678912345678','0478-44223333','流沙河'),
		  ('唐僧','321245678912345678','0478-44443333','女儿国')
		  select * from userInfo
insert into cardInfo(cardID,savingType,balance,customerID)
	values('1010 3576 1234 567','活期','1000','1'),
		  ('1010 3576 1212 117','定期','1','2'),
		  ('1010 3576 1212 113','定期','1','3')

--1.将用户“孙悟空”开卡时的初始密码更改为“611234”
 update cardInfo set pass = '611234' where customerID = 1
 select * from cardInfo
--2.用两条SQL语句实现孙悟空要取钱(取200)的操作，先向交易信息表插入一条取钱的交易记录，然后在孙悟空账上的余额减200
--注意：先要将用户孙悟空的用户编号找到，再根据用户编号找到卡号，再根据银行卡号来插入交易记录和修改账上余额
insert into transinfo(transdate,transtype,cardid,transmoney)
values ('支取','1010 3576 1234 567','200')
update cardInfo set balance = balance - 200 where cardID = (select cardID from cardInfo where customerID = 1)
--3.用同上题一样的方法实现沙和尚存钱的操作(存300)
insert into transInfo(cardID, transType, transMoney) values('存入', '1010 3576 1234 567', 300)
update cardInfo set balance = balance + 300 where cardID = (select cardID from cardInfo where customerID = 2)
--4.唐僧的卡丢了，需要挂失，将唐僧的银行卡的是否挂失字段的值改为“是”
update cardInfo set IsReportLoss = '是' where cardID = (select cardID from cardInfo where customerID = 3)
--5.查询出最近10天开户的银行卡的信息
select * from cardInfo where dateadd (dd,-10,getdate())>openDate
--6.查询交易金额最大的银行卡信息，子查询实现
select * from transinfo where transmoney=(select max(transmoney) from transinfo)
--7.再交易信息表中，将总的交易金额，支取的交易金额，存入的交易金额查询出来并输出显示
select '总交易金额:', sum(transMoney) from transInfo
union
select '支取交易金额:', transMoney from transInfo where transType = '支取'
union
select '支取交易金额:', transMoney from transInfo where transType = '存入'
-- 显示效果：
--  总交易金额：1400.00
--  支取交易金额：200.00
--  存入交易金额：1200.00

--8.催款提醒：假如由于某种业务的需求，每个月末，如果发现卡上账上余额少于200元的，将致电催款，请将卡上余额少于200元的用户的卡号和用户ID查询出来
select cardID, customerID from	cardInfo where balance < 200
